﻿/*******************************************************************************
 * Copyright @ 2017 YunSpace.Framework 版权所有
 * Author: GaoTang
 * Description: YunSpace 快速开发平台
 * Website：http://www.yunspace.com.cn
*********************************************************************************/
#region USING SYSTEM

using System.Collections.Generic;
using YunSpace.Core;
using YunSpace.Data;
using YunSpace.Domain.Entity.SiteLibraryManage;
using YunSpace.Domain.IRepository.SiteLibraryManage;
using YunSpace.Domain.ViewModel.SiteMgr.SalesTarget;

#endregion

namespace YunSpace.Repository.SiteLibraryManage
{
    public class ActivitiesCaseRepository : RepositoryBase<ActivitiesCaseEntity>, IActivitiesCaseRepository
    {
        #region 场地台账

        /// <summary>
        /// 获取场地台账列表
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        public List<ActivitiesCaseBillDto> GetActivitiesCaseBillDtos(Pagination pagination, GetActivitiesCaseBillGridJsonRequest request)
        {
            var cityIdFilterSql = request.CityId.IsNullOrEmpty() ? "" : $" AND a.F_Id = '{request.CityId}'";
            var keywordFilterSql = request.Keyword.IsNullOrEmpty() ? "" : $" AND (ac.F_ActivitiesTitle LIKE '%{request.Keyword}%' || s.title LIKE '%{request.Keyword}%')";

            var sql = $@"SELECT  ac.F_ActivitiesTitle AS ActivitiesTitle ,
                                ac.F_ActivitiesType AS ActivitiesType ,
                                ac.F_StarDate AS ActivitiesStarDate ,
                                ac.F_EndDate ActivitiesEndDate ,
                                s.F_Id AS SiteId ,
                                s.title AS SiteTitle ,
                                d.F_ItemName AS SiteType ,
                                s.F_contact AS Contact ,
                                oc.F_LastModifyTime AS SignedTime ,
                                ou.F_FeeType AS FeeType ,
                                ou.F_SpendCash AS SpendCash ,
                                ou.F_IncomeCash AS IncomeCash ,
                                ou.F_Tax AS Tax ,
                                ou.F_AfterTaxProfit ,
                                ou.F_ProfitMargin AS ProfitMargin ,
                                u.F_RealName AS Seller ,
                                u2.F_RealName AS DbAdminId ,
                                u3.F_RealName AS MochaItom ,
                                ognz.F_FullName AS Department ,
                                a.F_FullName AS CityName
                        FROM    Site_ActivitiesCase AS ac
                                INNER JOIN SL_Site AS s ON s.F_Id = ac.F_SiteId
                                LEFT JOIN Sys_ItemsDetail d ON d.F_ItemCode = s.site_type
                                                               AND d.F_ItemId = '04ded926-33b1-4705-9070-41ff554acfd8'
                                LEFT JOIN Sys_Area AS a ON a.F_Id = s.city_id
                                LEFT JOIN Site_Client_OrderSignContract AS oc ON oc.F_SiteId = s.F_Id
                                                                                 AND oc.F_State = 1
                                LEFT JOIN Site_Client_Order AS o ON o.F_Id = oc.F_OrderId
                                LEFT JOIN Site_Client_OrderUsing AS ou ON ou.F_OrderId = o.F_Id
                                LEFT JOIN Sys_User AS u ON u.F_Id = o.F_FlowUserId
                                LEFT JOIN Sys_User AS u2 ON u2.F_Id = s.db_admin_id
                                LEFT JOIN Sys_User AS u3 ON u3.F_Id = s.Mocha_ITOM
                                LEFT JOIN Sys_Organize AS ognz ON ognz.F_Id = u.F_DepartmentId
                        WHERE 0 = 0 {cityIdFilterSql} {keywordFilterSql}";

            var startIndex = pagination.rows * (pagination.page - 1);
            var endIndex = startIndex + pagination.rows;

            sql = $"{sql} ORDER BY ac.{pagination.sidx} LIMIT {startIndex},{endIndex};";
            pagination.records = GetActivitiesCaseBillDtosCount(cityIdFilterSql + keywordFilterSql);

            return GetColumnList<ActivitiesCaseBillDto>(sql);
        }

        /// <summary>
        /// 获取场地台账数量
        /// </summary>
        /// <returns></returns>
        private int GetActivitiesCaseBillDtosCount(string filterSql)
        {
            var sql = $@"SELECT  COUNT(1)
                        FROM    Site_ActivitiesCase AS ac
                                INNER JOIN SL_Site AS s ON s.F_Id = ac.F_SiteId
                                LEFT JOIN Sys_ItemsDetail d ON d.F_ItemCode = s.site_type
                                                               AND d.F_ItemId = '04ded926-33b1-4705-9070-41ff554acfd8'
                                LEFT JOIN Sys_Area AS a ON a.F_Id = s.city_id
                                LEFT JOIN Site_Client_OrderSignContract AS oc ON oc.F_SiteId = s.F_Id
                                                                                 AND oc.F_State = 1
                                LEFT JOIN Site_Client_Order AS o ON o.F_Id = oc.F_OrderId
                                LEFT JOIN Site_Client_OrderUsing AS ou ON ou.F_OrderId = o.F_Id
                                LEFT JOIN Sys_User AS u ON u.F_Id = o.F_FlowUserId
                                LEFT JOIN Sys_User AS u2 ON u2.F_Id = s.db_admin_id
                                LEFT JOIN Sys_User AS u3 ON u3.F_Id = s.Mocha_ITOM
                                LEFT JOIN Sys_Organize AS ognz ON ognz.F_Id = u.F_DepartmentId
                        WHERE 0 = 0 {filterSql}";
            return Single<int>(sql);
        }

        #endregion 

        #region private methods

        #endregion private methods

    }
}

